
from sqlalchemy import *

metadata = BoundMetaData('sqlite:///min_db.db')


class Employee(object):
    def __init__(self, name):
        self.name = name
    def __repr__(self):
        return self.__class__.__name__ + " " + self.name

class Manager(Employee):
    def __init__(self, name, manager_data):
        self.name = name
        self.manager_data = manager_data
    def __repr__(self):
        return self.__class__.__name__ + " " + self.name + " " +  self.manager_data

class Engineer(Employee):
    def __init__(self, name, engineer_info):
        self.name = name
        self.engineer_info = engineer_info
    def __repr__(self):
        return self.__class__.__name__ + " " + self.name + " " +  self.engineer_info

class Task(object):
    def __init__(self, task_desc):
        self.description = task_desc
        pass
    def __repr__(self):
        return self.description
        pass

employees = Table('employees', metadata, 
    Column('person_id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('type', String(30)))

engineers = Table('engineers', metadata, 
   Column('person_id', Integer, ForeignKey('employees.person_id'), primary_key=True),
   Column('engineer_info', String(50)),
  )

managers = Table('managers', metadata, 
   Column('person_id', Integer, ForeignKey('employees.person_id'), primary_key=True),
   Column('manager_data', String(50)),
   )


tasks = Table('tasks', metadata,
        Column('task_id', Integer, primary_key=True),
        Column('person_id', Integer, ForeignKey('engineers.person_id')),
        Column('description', String(64))
        )

metadata.drop_all()
metadata.engine.echo = True
metadata.create_all()

person_join = polymorphic_union(
    {
        'engineer':employees.join(engineers),
        'manager':employees.join(managers),
        'person':employees.select(employees.c.type=='person'),
    }, None, 'pjoin')

person_mapper = mapper(Employee, employees, select_table=person_join, polymorphic_on=person_join.c.type, polymorphic_identity='person')
mapper(Task, tasks)
mapper(Engineer, engineers, inherits=person_mapper, polymorphic_identity='engineer', properties = { 'tasks' : relation(Task) })
mapper(Manager, managers, inherits=person_mapper, polymorphic_identity='manager')

session = create_session()

tq = Engineer("Ivar", "Silly worker drone")
tq.tasks.append( Task('Clean your room'))
tq.tasks.append( Task('Install Xubuntu'))
tq.tasks.append( Task('Code..!'))

session.save(tq)
session.flush()

query = session.query(Employee)
ivar = query.selectfirst()
print "***"
print ivar
for task in ivar.tasks:
    print "  * %s" % (task)

